package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.it.networkAssets;

import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.networkassets.MaintenanceInformation;
import com.glsc.ngateway.common.api.platform.dto.it.networkassets.MaintenanceInformationDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.validation.constraints.NotNull;
import java.util.List;
import java.util.Set;

@Repository
public interface MaintenanceInformationRepo extends JpaRepository<MaintenanceInformation, Long> {

    @Query(value = "update t_gw_it_net_assets_maintenance_information " +
            "set serial_num = :serialNum " +
            "where id in :ids " +
            "and deleted = 0 " +
            "", nativeQuery = true)
    void connectMains(@NotNull @Param("ids") Set<Long> ids, @NotNull @Param("serialNum") String serialNum);

    @Query(value = "update t_gw_it_net_assets_maintenance_information " +
            "set serial_num = null " +
            "where id in :ids " +
            "and deleted = 0 " +
            "", nativeQuery = true)
    void unconnectMains(@NotNull @Param("ids") Set<Long> ids);

    @Query(value = "select * from t_gw_it_net_assets_maintenance_information h " +
            "where deleted = 0 " +
            "and if( :#{#mtInfo.serialNum} is not null, h.serial_num like CONCAT('%', :#{#mtInfo.serialNum}, '%'), 1=1 ) " +
            "and if( :#{#mtInfo.maintenanceInfo} is not null, h.maintenance_info like CONCAT('%', :#{#mtInfo.maintenanceInfo}, '%'), 1=1 ) " +
            "and if( :#{#mtInfo.supplier} is not null, h.supplier like CONCAT('%', :#{#mtInfo.supplier}, '%'), 1=1 ) " +
            "and if( :#{#mtInfo.start} is not null, h.start >= :#{#mtInfo.start}, 1=1 ) " +
            "and if( :#{#mtInfo.end} is not null, h.end <= :#{#mtInfo.end}, 1=1 ) " +
            "and if( :#{#mtInfo.bindOa} is not null, h.bind_oa like CONCAT('%', :#{#mtInfo.bindOa}, '%'), 1=1 ) " +
            "", nativeQuery = true)
    Page<MaintenanceInformation> page(Pageable pageable, @NotNull @Param("mtInfo") MaintenanceInformationDto mtInfo);

    @Query(value = "select * from t_gw_it_net_assets_maintenance_information h " +
            "where deleted = 0 " +
            "and if( :#{#mtInfo.serialNum} is not null, h.serial_num like CONCAT('%', :#{#mtInfo.serialNum}, '%'), 1=1 ) " +
            "and if( :#{#mtInfo.maintenanceInfo} is not null, h.maintenance_info like CONCAT('%', :#{#mtInfo.maintenanceInfo}, '%'), 1=1 ) " +
            "and if( :#{#mtInfo.supplier} is not null, h.supplier like CONCAT('%', :#{#mtInfo.supplier}, '%'), 1=1 ) " +
            "and if( :#{#mtInfo.start} is not null, h.start >= :#{#mtInfo.start}, 1=1 ) " +
            "and if( :#{#mtInfo.end} is not null, h.end <= :#{#mtInfo.end}, 1=1 ) " +
            "and if( :#{#mtInfo.bindOa} is not null, h.bind_oa like CONCAT('%', :#{#mtInfo.bindOa}, '%'), 1=1 ) " +
            "", nativeQuery = true)
    List<MaintenanceInformation> queryAll(@NotNull @Param("mtInfo") MaintenanceInformationDto param);

    List<MaintenanceInformation> findAllBySerialNum(String serialNum);

}
